const QUERY_SQL = `select * from users`;
const QUERY_SQL_By_Username = `select * from users where username=?`;
const INSERT_User = `INSERT INTO users(username, count, password) VALUES(?, ?, ?)`
const QUERY_SQL_By_Count = `select uid from users where count=?`
const QUERY_SQL_By_Count_All = `select * from users where count=?`
const QUERY_Food_material_SQL = `
	SELECT * FROM fooddetails WHERE main_material LIKE ? AND main_material LIKE ? AND main_material LIKE ?
`
const QUERY_Food_SQL_ID = `select food_id from fooddetails where title=?`
const QUERY_COLLECTION_SQL_By_Uid = `select * from user_collection where uid=?`
const QUERY_Food_dim_SQL = `
	select *
	from fooddetails 
	where title like?
	`;
const QUERY_Food_SQL = `
	select *
	from fooddetails 
	where title=?
	`;
const QUERY_Food_Kind_SQL =
	`
	select * 
	from fooddetails 
	where kind like? 
	
`;
const QUERY_Food_id_SQL =
	`
	select * 
	from fooddetails 
	where food_id=? 
	
`;
const QUERY_COMMUNITY_SQL=`select * from community `
const INSERT_Food_step_Upload = `INSERT INTO foods_steps (stepName, stepNum, stepImage, stepDescribe, food_id) VALUES (?,?,?,?,?)`
const QUERY_Food_step_SQL = `select * from foods_steps where food_id=?`
const QUERY_COLLECTION_SQL = `select * from user_collection where uid= ? and food_id=? `
const QUERY_BROWSE_SQL = `select * from browsing_history where uid= ? and food_id=? `
const QUERY_BROWSE_SQL_By_Uid = `select * from browsing_history where uid= ?`
// const QUERY_Food_SQL=`select * from foods_steps where stepName like?`
const INSERT_SQL = `INSERT INTO users SET ?`;
const INSERT_FoodSQL = `INSERT INTO fooddetails SET ?`;
const INSERT_FoodStepSQL = `INSERT INTO foods_steps SET ?`;
const INSERT_Food_Upload = `INSERT INTO fooddetails (title,  coverImage,main_material, excipient) VALUES (?, ?, ?, ?)`
const INSERT_USER_COLLECTION = `INSERT IGNORE INTO user_collection (uid, food_id,collect_time,food_img,food_title) VALUES (?, ?, ?,?,?)`
const INSERT_Community_Upload=`INSERT INTO community (uid,title, imageUrl,main_material, excipient,user_img,user_name,stepImage,stepName,food_id) VALUES (?,?,?,?,?,?,?,?,?,?)`
const INSERT_BROWSE_SQL = `INSERT INTO browsing_history (uid, food_id, browse_time,food_title,food_img) VALUES (?, ?, ?,?,?)`

const UPDATE_SQL = `UPDATE t_user SET username=? WHERE id=?`;
const UPDATE_false_COLLCTION = `UPDATE fooddetails SET is_collect=0 WHERE food_id=?`
const UPDATE_COLLCTION = `UPDATE fooddetails SET is_collect=1 WHERE food_id=?`
const UPDATE_USER_IMG=`UPDATE users SET user_img=? WHERE uid=?`
const DELETE_SQL = `DELETE FROM fooddetails WHERE id=?`;

const DELETE_Same_SQL = `DELETE FROM fooddetails WHERE food_id NOT IN (
	SELECT t.min_id FROM (

		SELECT MIN(food_id) AS min_id FROM fooddetails GROUP BY title
  ) t
)`;
const DELETE_Same_steps_SQL = `DELETE FROM foods_steps WHERE stepId NOT IN (
	SELECT t.min_id FROM (

		SELECT MIN(stepId) AS min_id FROM foods_steps GROUP BY stepName
  ) t
)`;
const DELETE_USER_COLLECTION = `DELETE FROM user_collection WHERE uid=? and food_id=?`
module.exports = {
	QUERY_Food_dim_SQL,
	QUERY_SQL, QUERY_Food_step_SQL,
	QUERY_Food_Kind_SQL, INSERT_SQL,
	UPDATE_SQL,
	DELETE_SQL,
	INSERT_FoodSQL,
	DELETE_Same_SQL,
	INSERT_FoodStepSQL,
	DELETE_Same_steps_SQL,
	QUERY_Food_SQL,
	QUERY_SQL_By_Username,
	INSERT_User,
	QUERY_SQL_By_Count,
	INSERT_Food_Upload,
	INSERT_USER_COLLECTION,
	DELETE_USER_COLLECTION,
	QUERY_COLLECTION_SQL,
	INSERT_BROWSE_SQL,
	QUERY_BROWSE_SQL,
	QUERY_COLLECTION_SQL_By_Uid,
	QUERY_BROWSE_SQL_By_Uid,
	QUERY_Food_id_SQL,
	QUERY_SQL_By_Count_All,
	QUERY_Food_material_SQL,
	UPDATE_false_COLLCTION,
	UPDATE_COLLCTION,
	INSERT_Food_step_Upload,
	UPDATE_USER_IMG,
	QUERY_Food_SQL_ID,
	INSERT_Community_Upload,
	QUERY_COMMUNITY_SQL
};